/**
 * 
 */
package com.depot.auth.dao;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import com.base.KeyGenerator;
import com.depot.domains.AppRole;
import com.soar.BaseActionSupport;
import com.soar.context.Environment;
import com.soar.jdbc.dbhelper.DbParameter;
import com.soar.jdbc.dbhelper.DbTemplates;
import com.soar.jdbc.dbhelper.SameTypes;
import com.soar.jdbc.dbhelper.SortTypes;
import com.soar.jdbc.dbhelper.SqlTypes;
import com.soar.jdbc.dbhelper.WhereTypes;
import com.soar.util.StringUtils;

/**
 * 
 * @author zhangaoxiang
 * @version $Revision$ - $Author$ at $Date$
 */
public class AppRoleDao
{
	/**
	 * 应用角色列表
	 * 
	 * @param env
	 * @param domain
	 * @param action
	 * @return
	 * @throws Exception
	 */
	public static List<HashMap<String, String>> select(Environment env, AppRole domain, BaseActionSupport action) throws SQLException
	{
		DbParameter param = new DbParameter("APP_ROLE T1 LEFT JOIN USER_ROLE T2 ON T2.ROLE_ID = T1.ID");
		StringBuilder sb = new StringBuilder();
		sb.append("T1.ID,");
		sb.append("T1.ROLE_NAME,");
		sb.append("T1.ROLE_CODE,");
		sb.append("T1.REMARK,");
		sb.append("T1.CREATE_USER,");
		sb.append("T1.CREATE_USER_ID,");
		sb.append("T1.CREATE_DATE,");
		sb.append("T1.UPDATE_USER,");
		sb.append("T1.UPDATE_USER_ID,");
		sb.append("T1.UPDATE_DATE");
		param.setSelectFields(sb.toString() + ",COUNT(T2.ROLE_ID) CT");
		if (StringUtils.isNoEmpty(domain.getSearchRoleName()))
		{
			param.setWhereParams("T1.ROLE_NAME", domain.getSearchRoleName(), SqlTypes.VARCHAR, WhereTypes.AND, SameTypes.LIKE);
		}
		if (StringUtils.isNoEmpty(domain.getSearchRoleCode()))
		{
			param.setWhereParams("T1.ROLE_CODE", domain.getSearchRoleCode(), SqlTypes.VARCHAR, WhereTypes.AND, SameTypes.LIKE);
		}
		if (StringUtils.isNoEmpty(domain.getSearchRemark()))
		{
			param.setWhereParams("T1.REMARK", domain.getSearchRemark(), SqlTypes.VARCHAR, WhereTypes.AND, SameTypes.LIKE);
		}
		param.setGroup(sb.toString());
		if (action.getSys_p_sort_field().equals(""))
		{
			action.setSys_p_sort_field("T1.UPDATE_DATE");
			action.setSys_p_sort_method(SortTypes.DESC);
		}
		param.setOrderParams(action.getSys_p_sort_field(), action.getSys_p_sort_method());
		return DbTemplates.select(env.getConnection(), param, action);
	}

	public static List<HashMap<String, String>> select(Environment env, AppRole domain) throws SQLException
	{
		DbParameter param = new DbParameter("APP_ROLE T1");
		StringBuilder sb = new StringBuilder();
		sb.append("T1.ID,");
		sb.append("T1.ROLE_NAME,");
		sb.append("T1.ROLE_CODE,");
		sb.append("T1.REMARK,");
		sb.append("T1.CREATE_USER,");
		sb.append("T1.CREATE_USER_ID,");
		sb.append("T1.CREATE_DATE,");
		sb.append("T1.UPDATE_USER,");
		sb.append("T1.UPDATE_USER_ID,");
		sb.append("T1.UPDATE_DATE");
		param.setSelectFields(sb.toString());
		if (StringUtils.isNoEmpty(domain.getSearchRoleName()))
		{
			param.setWhereParams("T1.ROLE_NAME", domain.getSearchRoleName(), SqlTypes.VARCHAR, WhereTypes.AND, SameTypes.LIKE);
		}
		if (StringUtils.isNoEmpty(domain.getSearchRoleCode()))
		{
			param.setWhereParams("T1.ROLE_CODE", domain.getSearchRoleCode(), SqlTypes.VARCHAR, WhereTypes.AND, SameTypes.LIKE);
		}
		if (StringUtils.isNoEmpty(domain.getSearchRemark()))
		{
			param.setWhereParams("T1.REMARK", domain.getSearchRemark(), SqlTypes.VARCHAR, WhereTypes.AND, SameTypes.LIKE);
		}
		return DbTemplates.select(env.getConnection(), param);
	}

	/**
	 * 应用菜单列表
	 * 
	 * @param env
	 * @param domain
	 * @return
	 * @throws Exception
	 * */
	public static List<HashMap<String, String>> selectMenu(Environment env, AppRole domain) throws SQLException
	{
		DbParameter param = new DbParameter();
		if (domain.getId().indexOf(",") != -1)
		{
			param = new DbParameter("APP_MENU T1 LEFT JOIN APP_ROLE_AUTH T2 ON T1.ID = T2.MENU_ID AND T2.ROLE_ID IN " + param.getInSQL(domain.getId().split(",")));
			StringBuilder sb = new StringBuilder();
			sb.append("T1.ID,");
			sb.append("T1.PID,");
			sb.append("T1.MU_NAME,");
			sb.append("T1.MU_ORDER,");
			sb.append("T1.MU_ICON,");
			sb.append("T1.MU_URL,");
			sb.append("T1.MU_DISPLAY,");
			sb.append("T1.MU_PLATFORM,");
			sb.append("T1.MU_LEVEL,");
			sb.append("T1.OPEN_MODE,");
			sb.append("T1.MU_MODE,");
			sb.append("T1.MU_POSTION,");
			sb.append("T1.REQUEST_METHOD,");
			sb.append("T1.MODE_CODE,");
			sb.append("T1.CREATE_USER_ID,");
			sb.append("T1.CREATE_USER,");
			sb.append("T1.CREATE_DATE,");
			sb.append("T1.UPDATE_USER_ID,");
			sb.append("T1.UPDATE_USER,");
			sb.append("T1.UPDATE_DATE");
			param.setSelectFields(sb.toString() + ",COUNT(T2.ID) CT");
			// param.setWhereParams("T2.ROLE_ID", domain.getId(), SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
			// param.setWhereParams("T1.ID", "T2.MENU_ID(+)", SqlTypes.CUSTOM, WhereTypes.AND, SameTypes.YES);
			param.setGroup(sb.toString());
			param.setOrderParams("MU_ORDER", SortTypes.ASC);
			return DbTemplates.select(env.getConnection(), param);
		}
		else
		{
			param = new DbParameter("APP_MENU T1 LEFT JOIN APP_ROLE_AUTH T2 ON T1.ID = T2.MENU_ID AND T2.ROLE_ID = '" + domain.getId() + "'");
			StringBuilder sb = new StringBuilder();
			sb.append("T1.ID,");
			sb.append("T1.PID,");
			sb.append("T1.MU_NAME,");
			sb.append("T1.MU_ORDER,");
			sb.append("T1.MU_ICON,");
			sb.append("T1.MU_URL,");
			sb.append("T1.MU_DISPLAY,");
			sb.append("T1.MU_PLATFORM,");
			sb.append("T1.MU_LEVEL,");
			sb.append("T1.OPEN_MODE,");
			sb.append("T1.MU_MODE,");
			sb.append("T1.MU_POSTION,");
			sb.append("T1.REQUEST_METHOD,");
			sb.append("T1.MODE_CODE,");
			sb.append("T1.CREATE_USER_ID,");
			sb.append("T1.CREATE_USER,");
			sb.append("T1.CREATE_DATE,");
			sb.append("T1.UPDATE_USER_ID,");
			sb.append("T1.UPDATE_USER,");
			sb.append("T1.UPDATE_DATE");
			param.setSelectFields(sb.toString() + ",COUNT(T2.ID) CT");
			// param.setWhereParams("T2.ROLE_ID", domain.getId(), SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
			// param.setWhereParams("T1.ID", "T2.MENU_ID(+)", SqlTypes.CUSTOM, WhereTypes.AND, SameTypes.YES);
			param.setGroup(sb.toString());
			param.setOrderParams("MU_ORDER", SortTypes.ASC);
			return DbTemplates.select(env.getConnection(), param);
		}
	}

	public static String saveRoleMenu(Environment env, AppRole domain) throws SQLException
	{
		String[] ids = domain.getId().split(",");
		// 删除原有的
		DbParameter param = new DbParameter("APP_ROLE_AUTH");
		param.setWhereParams("ROLE_ID", param.getInSQL(ids), SqlTypes.CUSTOM, WhereTypes.AND, SameTypes.IN);
		DbTemplates.delete(env.getConnection(), param);

		if (domain.getIds() != null)
		{
			for (int i = 0; i < domain.getIds().length; i++)
			{
				for (int j = 0; j < ids.length; j++)
				{
					if (!hasRecord(env, ids[j], domain.getIds()[i]))
					{
						param = new DbParameter("APP_ROLE_AUTH T1");
						param.setParams("ID", KeyGenerator.getInstance().getKeyId(env.getConnection()), SqlTypes.CHAR);
						param.setParams("ROLE_ID", ids[j], SqlTypes.CHAR);
						param.setParams("MENU_ID", domain.getIds()[i], SqlTypes.CHAR);
						param.setParams("CREATE_USER_ID", env.getLoginUser().getUserId(), SqlTypes.CHAR);
						param.setParams("CREATE_USER", env.getLoginUser().getUserName(), SqlTypes.VARCHAR);
						param.setParams("CREATE_DATE", "sysdate", SqlTypes.CUSTOM);
						param.setParams("UPDATE_USER_ID", env.getLoginUser().getUserId(), SqlTypes.CHAR);
						param.setParams("UPDATE_USER", env.getLoginUser().getUserName(), SqlTypes.VARCHAR);
						param.setParams("UPDATE_DATE", "sysdate", SqlTypes.CUSTOM);
						// param.setParams("MU_DISPLAY", "1", SqlTypes.CHAR);
						if (!DbTemplates.insert(env.getConnection(), param))
						{
							env.getConnection().rollback();
							return "0";
						}
					}
				}
			}
		}
		return "1";
	}

	/**
	 * 检查是否已有记录
	 * 
	 * @param env
	 * @param rid
	 * @param mid
	 * @return
	 * @throws SQLException
	 */
	public static boolean hasRecord(Environment env, String rid, String mid) throws SQLException
	{
		DbParameter param = new DbParameter("APP_ROLE_AUTH");
		param.setWhereParams("ROLE_ID", rid, SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
		param.setWhereParams("MENU_ID", mid, SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
		if (DbTemplates.select(env.getConnection(), param).size() > 0)
		{
			return true;
		}
		else
		{
			return false;
		}
	}

	/**
	 * 应用角色更新
	 * 
	 * @param env
	 * @param domain
	 * @return
	 * @throws Exception
	 */
	public static String doSave(Environment env, AppRole domain) throws SQLException
	{
		DbParameter param = new DbParameter("APP_ROLE T1");
		param.setSelectFields("1");
		param.setWhereParams("T1.ROLE_NAME", domain.getRoleName(), SqlTypes.VARCHAR, WhereTypes.AND, SameTypes.YES);
		if (StringUtils.isNoEmpty(domain.getId()))
		{
			param.setWhereParams("T1.ID", domain.getId(), SqlTypes.CHAR, WhereTypes.AND, SameTypes.NO);
		}
		if (DbTemplates.select(env.getConnection(), param).size() > 0)
		{
			return "2";
		}

		if (StringUtils.isEmpty(domain.getId()))
		{// ID为空 新增
			param = new DbParameter("APP_ROLE T1");
			param.setParams("ID", KeyGenerator.getInstance().getKeyId(env.getConnection()), SqlTypes.CHAR);
			param.setParams("ROLE_NAME", domain.getRoleName(), SqlTypes.VARCHAR);
			param.setParams("ROLE_CODE", domain.getRoleCode(), SqlTypes.VARCHAR);
			param.setParams("REMARK", domain.getRemark(), SqlTypes.VARCHAR);
			param.setParams("CREATE_USER_ID", env.getLoginUser().getUserId(), SqlTypes.CHAR);
			param.setParams("CREATE_USER", env.getLoginUser().getUserName(), SqlTypes.VARCHAR);
			param.setParams("CREATE_DATE", "sysdate", SqlTypes.CUSTOM);
			param.setParams("UPDATE_USER_ID", env.getLoginUser().getUserId(), SqlTypes.CHAR);
			param.setParams("UPDATE_USER", env.getLoginUser().getUserName(), SqlTypes.VARCHAR);
			param.setParams("UPDATE_DATE", "sysdate", SqlTypes.CUSTOM);
			if (DbTemplates.insert(env.getConnection(), param))
			{
				return "1";
			}
			else
			{
				return "0";
			}
		}
		else
		{// ID不为空 修改
			param = new DbParameter("APP_ROLE T1");
			param.setParams("ROLE_NAME", domain.getRoleName(), SqlTypes.VARCHAR);
			param.setParams("ROLE_CODE", domain.getRoleCode(), SqlTypes.VARCHAR);
			param.setParams("REMARK", domain.getRemark(), SqlTypes.VARCHAR);
			param.setParams("UPDATE_USER_ID", env.getLoginUser().getUserId(), SqlTypes.CHAR);
			param.setParams("UPDATE_USER", env.getLoginUser().getUserName(), SqlTypes.VARCHAR);
			param.setParams("UPDATE_DATE", "sysdate", SqlTypes.CUSTOM);
			param.setWhereParams("T1.ID", domain.getId(), SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
			if (DbTemplates.update(env.getConnection(), param))
			{
				return "1";
			}
			else
			{
				return "0";
			}
		}
	}

	/**
	 * 应用角色删除
	 * 
	 * @param env
	 * @param domain
	 * @return
	 * @throws Exception
	 */
	public static String doDelete(Environment env, AppRole domain) throws SQLException
	{
		DbParameter param = null;
		// 删除角色应用
		param = new DbParameter("APP_ROLE_AUTH T1");
		if (domain.getIds() != null)
		{
			// String[] ids = new String[domain.getIds().length];
			// for (int i = 0; i < domain.getIds().length; i++)
			// {
			// ids[i] = StringUtils.delimitedListToStringArray(domain.getIds()[i], "_")[0];
			// }
			param.setWhereParams("T1.ROLE_ID", param.getInSQL(domain.getIds()), SqlTypes.CUSTOM, WhereTypes.NOT, SameTypes.IN);
		}
		else
		{
			param.setWhereParams("T1.ROLE_ID", domain.getId(), SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
		}
		// if (!DbTemplates.delete(env.getConnection(), param))
		// {
		// env.getConnection().rollback();
		// return "0";
		// }
		DbTemplates.delete(env.getConnection(), param);

		// 删除角色
		param = new DbParameter("APP_ROLE T1");
		if (domain.getIds() != null)
		{
			String[] ids = new String[domain.getIds().length];
			for (int i = 0; i < domain.getIds().length; i++)
			{
				ids[i] = StringUtils.delimitedListToStringArray(domain.getIds()[i], "_")[0];
			}
			param.setWhereParams("T1.ID", param.getInSQL(ids), SqlTypes.CUSTOM, WhereTypes.NOT, SameTypes.IN);
		}
		else
		{
			param.setWhereParams("T1.ID", domain.getId(), SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
		}

		if (!DbTemplates.delete(env.getConnection(), param))
		{
			env.getConnection().rollback();
			return "0";
		}

		return "1";
	}

	/**
	 * 应用角色单行查询
	 * 
	 * @param env
	 * @param domain
	 * @return
	 * @throws Exception
	 */
	public static HashMap<String, String> doShow(Environment env, AppRole domain) throws SQLException
	{
		DbParameter param = new DbParameter("APP_ROLE T1");
		StringBuilder sb = new StringBuilder();
		sb.append("T1.ID,");
		sb.append("T1.ROLE_NAME,");
		sb.append("T1.ROLE_CODE,");
		sb.append("T1.REMARK,");
		sb.append("T1.CREATE_USER,");
		sb.append("T1.CREATE_USER_ID,");
		sb.append("T1.CREATE_DATE,");
		sb.append("T1.UPDATE_USER,");
		sb.append("T1.UPDATE_USER_ID,");
		sb.append("T1.UPDATE_DATE");
		param.setSelectFields(sb.toString());
		param.setWhereParams("T1.ID", domain.getId(), SqlTypes.CHAR, WhereTypes.AND, SameTypes.YES);
		return DbTemplates.select(env.getConnection(), param).get(0);
	}

	public static List<HashMap<String, String>> doShowPl(Environment env, AppRole domain) throws SQLException
	{
		DbParameter param = new DbParameter("APP_ROLE T1");
		StringBuilder sb = new StringBuilder();
		sb.append("T1.ID,");
		sb.append("T1.ROLE_NAME,");
		sb.append("T1.ROLE_CODE,");
		sb.append("T1.REMARK,");
		sb.append("T1.CREATE_USER,");
		sb.append("T1.CREATE_USER_ID,");
		sb.append("T1.CREATE_DATE,");
		sb.append("T1.UPDATE_USER,");
		sb.append("T1.UPDATE_USER_ID,");
		sb.append("T1.UPDATE_DATE");
		param.setSelectFields(sb.toString());
		param.setWhereParams("T1.ID", param.getInSQL(domain.getId().split(",")), SqlTypes.CUSTOM, WhereTypes.AND, SameTypes.IN);
		return DbTemplates.select(env.getConnection(), param);
	}

	public static List<HashMap<String, String>> getRoles(Environment env, AppRole domain) throws SQLException
	{
		DbParameter param = new DbParameter("APP_ROLE_AUTH T1");
		param.setSelectFields("T1.ID,T1.MENU_ID");
		param.setWhereParams("T1.ROLE_ID", param.getInSQL(domain.getIds()), SqlTypes.CUSTOM, WhereTypes.AND, SameTypes.IN);
		return DbTemplates.select(env.getConnection(), param);
	}
}